﻿'Description:装试质量检验相关业务
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:longb
'create Date:2022-06-09
'Modify by longb
'Rewriter:
'Rewrite Date:
Imports BusinessRule.IBusiness
Imports System.Text
Imports BusinessRule.DTO
Imports DatabaseAccess.DBUtility
Imports DatabaseAccess.IDAL
Imports DatabaseAccess.DALFactory
Imports System.Data.OracleClient
Public Class QulityCheckBusiness
    ''' <summary>
    ''' 查询【质量问题处置】大小件信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetQualityDealBigSmallData(ByVal para As Dictionary(Of String, String)) As DataTable
        Dim sb As New StringBuilder
        sb.AppendLine(<string>
                        WITH A AS(
	            SELECT im.WORKFLOWSTEPID,im.issueerpmaterialid,cm.collecterpmaterialid,pm.partmenuinfoid,pm.PRODUCTID,nvl(im.issueqty,0) issueqty,
	            cm.productname, cm.productbatchno,b.confirmqty,nvl(a.lossqty,0) lossqty
	            ,CASE WHEN eb.stepinfocount IS NULL THEN '未创建' WHEN eb.stepinfocount = 0 THEN '已创建'
	             WHEN eb.stepinfocount > 0 THEN '已完成' END status
	             ,CASE WHEN d.issueerpmaterialid IS NULL AND dd.issueerpmaterialid IS NULL THEN '未排故' ELSE '已排故' END DealStatus
	            FROM issueerpmaterial im
	            LEFT JOIN (SELECT SUM(imsi.lossqty) lossqty, imsi.issueerpmaterialid
	                       FROM issueerpmaterialscrapinfo imsi GROUP BY imsi.issueerpmaterialid) a
	            ON a.issueerpmaterialid = im.issueerpmaterialid
	            LEFT JOIN (SELECT SUM(imc.confirmqty) confirmqty, imc.issueerpmaterialid
	                       FROM issueerpmaterialconfirmation imc GROUP BY imc.issueerpmaterialid) b
	            ON b.issueerpmaterialid = im.issueerpmaterialid
	            LEFT JOIN partmenuinfo pm ON im.partmenuinfoid = pm.partmenuinfoid
	            LEFT JOIN collecterpmaterial cm ON im.collecterpmaterialid = cm.collecterpmaterialid
	            LEFT JOIN (SELECT SUM(DECODE(ex.stepinfoid, NULL, 0,1)) stepinfocount,ex.comcontainername,
	            ex.containerno,ex.productid FROM exchangebill ex GROUP BY ex.comcontainername, ex.containerno, ex.productid) eb
	            on eb.containerno = im.containerno and eb.comcontainername = cm.productbatchno and eb.productid=pm.PRODUCTID
	            LEFT JOIN dealfaultbill d ON d.issueerpmaterialid=im.issueerpmaterialid
	            LEFT JOIN dealfaultbilldetail dd ON dd.issueerpmaterialid=im.issueerpmaterialid
                      </string>.Value)
        sb.AppendFormat(" where b.confirmqty>0 AND im.WORKFLOWSTEPID ='{0}' AND im.CONTAINERNO ='{1}')", para("workflowstepid"), para("ContainerNo"))
        sb.AppendLine(<string>
                SELECT pb.productname,p.productid,p.description pdesc,pm.qtyrequired,nvl(a.confirmqty,0)confirmqty,nvl(a.lossqty,0) lossqty,nvl(a.issueqty,0) issueqty,status,DealStatus,productbatchno,issueerpmaterialid
                FROM WORKFLOWSTEP wfs
                LEFT JOIN Bom b ON b.WORKFLOWID =wfs.WORKFLOWID 
                LEFT JOIN productmateriallistitem pm ON pm.BOMID =b.BOMID 
                LEFT JOIN productbase pb1 ON pb1.productbaseid=pm.productbaseid  
                LEFT JOIN product p ON p.productid=NVL(pb1.revofrcdid,pm.productid) 
                LEFT JOIN productbase pb ON pb.productbaseid=p.productbaseid
                LEFT JOIN A a ON a.productname=pb.productname
                      </string>)
        sb.AppendFormat("WHERE wfs.WORKFLOWSTEPID='{0}' AND pm.specid IN (SELECT specid FROM spec WHERE specid=wfs.SPECID OR specbaseid=wfs.SPECBASEID) order by pb.productname  ", para("workflowstepid"))
        Return DatabaseHelper.Query_ReturnDatTable(sb.ToString)
    End Function
    ''' <summary>
    ''' 查询质量问题处置单信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetQulityQuestionDeal(ByVal para As Dictionary(Of String, String)) As DataTable
        Dim sb As New StringBuilder
        sb.AppendFormat("select * from QUALITYHISTORYRECORD where PRODUCTITEMNAME='{0}'", para("productitemname"))
        If para.ContainsKey("IssueERPMaterialID") Then
            If Not String.IsNullOrWhiteSpace(para("IssueERPMaterialID")) Then
                sb.AppendFormat(" AND ISSUEERPMATERIALID='{0}'", para("IssueERPMaterialID"))
            End If
        End If
        If para.ContainsKey("togetherflag") Then
            If Not String.IsNullOrWhiteSpace(para("togetherflag")) Then
                sb.AppendFormat(" AND TOGETHERFLAG='{0}'", para("togetherflag"))
            End If
        End If
        Return DatabaseHelper.Query_ReturnDatTable(sb.ToString)
    End Function
    ''' <summary>
    ''' 获取处置单最大顺序号
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetQulityQuestionDeal_MaxSequence() As String
        Dim maxSequence As String = "1"
        Dim sb As New StringBuilder
        sb.AppendFormat("SELECT DECODE(MAX(SEQUENCE),NULL,0,MAX(SEQUENCE))+1 SEQUENCE FROM QUALITYHISTORYRECORD")
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        If dt.Rows.Count > 0 Then
            maxSequence = dt.Rows(0)("SEQUENCE").ToString
        End If
        Return maxSequence
    End Function

    ''' <summary>
    ''' 查询--质量问题处置单信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetQualityQuestionDealExamine(ByVal para As Dictionary(Of String, String)) As DataTable
        Dim sb As New StringBuilder
        sb.AppendLine(<string>
                    SELECT QUALITYHISTORYRECORDID,DEALNAME,MFGORDERNAME,PRODUCTITEMNAME,CONTAINERNO,DECODE(STATUS,1,'已审核','待审核') STATUS,UNQUALIFIEDNUM,CHECKNO,
                    DECODE(PARTCATEGORY,'01','关键件','02','重要件','03','一般件','04','试验件','其他') PARTCATEGORY,UNQUALIFIEDDESCRIBE
                    ,w.WORKFLOWSTEPNAME,e2.FULLNAME DUTYNAME,f.FACTORYNAME,e.FULLNAME CREATENAME,
                    to_char(CREATETIME,'yyyy-MM-dd') CREATETIME,e1.FULLNAME CONFIRMNAME,to_char(CONFIRMDATE,'yyyy-MM-dd') CONFIRMDATE
                    FROM QUALITYHISTORYRECORD q
                    LEFT JOIN EMPLOYEE e ON e.employeeid=q.CREATEEMPLOYEEID
                    LEFT JOIN EMPLOYEE e1 ON  e1.employeeid=q.CONFIRMEMPLOYEEID
                    LEFT JOIN EMPLOYEE e2 ON  e2.employeeid=q.DUTYEMPLOYEEID
                    LEFT JOIN FACTORY f ON f.FACTORYID =q.FACTORYID
                    LEFT JOIN WORKFLOWSTEP w ON w.WORKFLOWSTEPID =q.WORKFLOWSTEPID
                    WHERE 1=1
                      </string>)
        If para.ContainsKey("dealname") Then
            If Not String.IsNullOrWhiteSpace(para("dealname")) Then
                sb.AppendFormat(" AND DEALNAME LIKE '%{0}%'", para("dealname")) '处置单号
            End If
        End If
        If para.ContainsKey("mfgordername") Then
            If Not String.IsNullOrWhiteSpace(para("mfgordername")) Then
                sb.AppendFormat(" AND MFGORDERNAME LIKE '%{0}%'", para("mfgordername")) '订单号
            End If
        End If
        If para.ContainsKey("productname") Then
            If Not String.IsNullOrWhiteSpace(para("productname")) Then
                sb.AppendFormat(" AND PRODUCTITEMNAME LIKE '%{0}%'", para("productname")) '件号
            End If
        End If
        If para.ContainsKey("startedate") Then
            If Not String.IsNullOrWhiteSpace(para("startedate")) Then
                sb.AppendFormat(" AND to_char(CREATETIME,'yyyy-MM-dd')>='{0}'", para("startedate")) '起始日期
            End If
        End If
        If para.ContainsKey("enddate") Then
            If Not String.IsNullOrWhiteSpace(para("enddate")) Then
                sb.AppendFormat(" AND to_char(CREATETIME,'yyyy-MM-dd')<='{0}'", para("enddate")) '结束日期
            End If
        End If

        Return DatabaseHelper.Query_ReturnDatTable(sb.ToString)
    End Function
    ''' <summary>
    ''' 质量问题处置单--【审核】
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function QualityQuestionDealExamine(ByVal para As Dictionary(Of String, String)) As Integer
        Dim sb As New StringBuilder
        sb.AppendFormat("UPDATE QUALITYHISTORYRECORD SET CONFIRMEMPLOYEEID='{0}',CONFIRMDATE=sysdate,STATUS=1 where QUALITYHISTORYRECORDID IN ('{1}')", para("employeeid"), para("qualityhistoryid").Replace(",", "','"))
        Return DatabaseHelper.ExecuteSql(sb.ToString)
    End Function
    ''' <summary>
    ''' 根据角色、分厂或员工名查询
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetEmployees(ByVal para As Dictionary(Of String, String)) As DataTable
        Dim sb As New StringBuilder
        sb.AppendLine(<string>
                        SELECT DISTINCT e.EMPLOYEEID ID,e.EMPLOYEENAME,e.FULLNAME NAME
                        FROM EMPLOYEE e 
                        LEFT JOIN EMPLOYEEROLE e2 ON e2.EMPLOYEEID =e.EMPLOYEEID 
                        LEFT JOIN ROLEDEF r ON r.ROLEID =e2.ROLEID
                        LEFT JOIN SESSIONVALUES s ON s.EMPLOYEEID=e.EMPLOYEEID 
                        LEFT JOIN FACTORY f ON f.FACTORYID =s.FACTORYID 
                        WHERE 1=1
                          </string>.Value)
        If para.ContainsKey("RoleName") Then
            If Not String.IsNullOrWhiteSpace(para("RoleName")) Then
                sb.AppendFormat(" and r.ROLENAME like '%{0}%' ", para("RoleName"))
            End If
        End If
        If para.ContainsKey("FactoryName") Then
            If Not String.IsNullOrWhiteSpace(para("FactoryName")) Then
                sb.AppendFormat(" and f.FACTORYNAME like '%{0}%' ", para("FactoryName"))
            End If
        End If
        If para.ContainsKey("Name") Then
            If Not String.IsNullOrWhiteSpace(para("Name")) Then
                sb.AppendFormat(" and FULLNAME like '%{0}%' ", para("Name"))
            End If
        End If
        Return DatabaseHelper.Query_ReturnDatTable(sb.ToString)
    End Function

    '----------------故检提交单------------------------------
    ''' <summary>
    ''' (分页)查询---故检提交单信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetGJCheckSubmitBill(ByVal para As Dictionary(Of String, String)) As uMESProcessResult
        Dim sb As New StringBuilder
        sb.AppendLine(<string>
                    SELECT  bill.ZSGJCHECKSUBMITBILLID,bill.ZSGJCHECKSUBMITBILLNO,DECODE(bill.STATUS,'0','待提交','1','审核中','2','审核完','3','已完成','其他') STATUS, 
                    m.MFGORDERNAME,u.ATTRIBUTEVALUE,p1.PRODUCTFAMILYNAME,bill.PRODUCTTYPE,to_char(bill.GJCHECKFINISHDATE,'yyyy-MM-dd')GJCHECKFINISHDATE,
                    bill.ISQUALIFIED,bill.DISCOVERPROPLEM,bill.TAKEMEASURES,bill.CONCLUSION,bill.MFGORDERID,
                    to_char(bill.CREATEDATE,'yyyy-MM-dd hh24:mi:ss')CREATEDATE,e.FULLNAME CREATENAME,
                    to_char(bill.SUBMITDATE,'yyyy-MM-dd hh24:mi:ss')SUBMITDATE,e2.FULLNAME SUBMITNAME,
                    to_char(bill.AUDITDATE,'yyyy-MM-dd hh24:mi:ss')AUDITDATE,e3.FULLNAME AUDITNAME
                    FROM ZSGJCheckSubmitBill bill 
                    LEFT JOIN MFGORDER m ON m.MFGORDERID =bill.MFGORDERID
                    LEFT JOIN Userattribute u ON u.parentid=m.mfgorderid AND u.userattributename='发动机' 
                    LEFT JOIN PRODUCT p ON p.PRODUCTID =m.PRODUCTID
                    LEFT JOIN workflowproductfamilylist wfp ON wfp.workflowid=p.workflowid
                    LEFT JOIN PRODUCTFAMILY p1 ON p1.PRODUCTFAMILYID =wfp.productfamilylistid
                    LEFT JOIN EMPLOYEE e ON e.EMPLOYEEID =bill.CREATEEMPLOYEEID
                    LEFT JOIN EMPLOYEE e2 ON e2.EMPLOYEEID =bill.SUBMITEMPLOYEEID
                    LEFT JOIN EMPLOYEE e3 ON e3.EMPLOYEEID =bill.AUDITEMPLOYEEID
                    WHERE 1=1
                      </string>)
        If para.ContainsKey("Machine") Then '发动机号
            If Not String.IsNullOrWhiteSpace(para("Machine")) Then
                sb.AppendFormat(" and u.ATTRIBUTEVALUE like '%{0}%' ", para("Machine"))
            End If
        End If
        If para.ContainsKey("MfgOrdername") Then '订单号
            If Not String.IsNullOrWhiteSpace(para("MfgOrdername")) Then
                sb.AppendFormat(" and m.MFGORDERNAME like '%{0}%' ", para("MfgOrdername"))
            End If
        End If
        If para.ContainsKey("Status") Then '状态
            If Not String.IsNullOrWhiteSpace(para("Status")) Then
                sb.AppendFormat(" and bill.STATUS='{0}' ", para("Status"))
            End If
        End If
        If para.ContainsKey("ProductType") Then '状态
            If Not String.IsNullOrWhiteSpace(para("ProductType")) Then
                sb.AppendFormat(" and bill.ProductType='{0}' ", para("ProductType"))
            End If
        End If
        If para.ContainsKey("startedate") Then
            If Not String.IsNullOrWhiteSpace(para("startedate")) Then
                sb.AppendFormat(" and to_char(bill.CREATEDATE,'yyyy-MM-dd')>='{0}' ", para("startedate"))
            End If
        End If
        If para.ContainsKey("enddate") Then
            If Not String.IsNullOrWhiteSpace(para("enddate")) Then
                sb.AppendFormat(" and to_char(bill.CREATEDATE,'yyyy-MM-dd')<='{0}' ", para("enddate"))
            End If
        End If
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = sb.ToString
        Return DatabaseHelper.RunProcedureForPage(paraPage)
    End Function
    ''' <summary>
    ''' 插入---故检提交单信息
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function InsertGJCheckSubmitBill(ByVal para As Dictionary(Of String, String)) As Boolean
        Dim sb As New StringBuilder
        sb.AppendLine("INSERT INTO ZSGJCheckSubmitBill(ZSGJCHECKSUBMITBILLID,ZSGJCHECKSUBMITBILLNO,MFGORDERID,PRODUCTTYPE,GJCHECKFINISHDATE,ISQUALIFIED,DISCOVERPROPLEM,TAKEMEASURES,CREATEDATE,CREATEEMPLOYEEID,STATUS)")
        sb.AppendFormat("VALUES('{0}','{1}','{2}','{3}',to_date('{4}','yyyy-MM-dd'),'{5}','{6}','{7}',sysdate,'{8}','0')", para("ZSGJCHECKSUBMITBILLID"), para("ZSGJCHECKSUBMITBILLNO"), para("MFGORDERID"), para("PRODUCTTYPE"), para("GJCHECKFINISHDATE"), para("ISQUALIFIED"), para("DISCOVERPROPLEM"), para("TAKEMEASURES"), para("CREATEEMPLOYEEID"))
        Return DatabaseHelper.ExecuteSql(sb.ToString) > 0
    End Function
    ''' <summary>
    ''' 更新---故检提交单信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function UpdateGJCheckSubmitBill(ByVal para As Dictionary(Of String, String)) As Boolean
        Dim sb As New StringBuilder
        sb.AppendLine(" UPDATE ZSGJCheckSubmitBill SET")
        If para.ContainsKey("PRODUCTTYPE") Then '保存更新
            sb.AppendFormat("PRODUCTTYPE='{0}',GJCHECKFINISHDATE=to_date('{1}','yyyy-MM-dd'),ISQUALIFIED='{2}',DISCOVERPROPLEM='{3}',TAKEMEASURES='{4}'", para("PRODUCTTYPE"), para("GJCHECKFINISHDATE"), para("ISQUALIFIED"), para("DISCOVERPROPLEM"), para("TAKEMEASURES"))
        End If
        If para.ContainsKey("SUBMITEMPLOYEEID") Then '提交更新
            sb.AppendFormat("STATUS='1',SUBMITDATE=sysdate,SUBMITEMPLOYEEID='{0}'", para("SUBMITEMPLOYEEID"))
        End If
        If para.ContainsKey("AUDITEMPLOYEEID") Then '审核完更新--QMS接口返回更新
            sb.AppendFormat("STATUS='2',AUDITDATE=sysdate,AUDITEMPLOYEEID='{0}',CONCLUSION='{1}'", para("AUDITEMPLOYEEID"), para("CONCLUSION"))
        End If
        If para.ContainsKey("Status") Then '完成更新--只更新状态
            sb.AppendFormat("Status='{0}'", para("Status"))
        End If
        sb.AppendFormat(" WHERE ZSGJCHECKSUBMITBILLID='{0}'", para("ZSGJCHECKSUBMITBILLID"))
        Return DatabaseHelper.ExecuteSql(sb.ToString) > 0
    End Function
    ''' <summary>
    ''' 查询---故检提交单总数
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetGJCheckSubmitBillCount() As String
        Dim Count = "1"
        Dim sb As New StringBuilder
        sb.AppendLine("SELECT COUNT(1)+1 CN FROM ZSGJCheckSubmitBill")
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        If dt.Rows.Count > 0 Then
            Count = dt.Rows("0")("CN").ToString
        End If
        Return Count
    End Function


    '-----------------部件入库------------------------
    Function GetMfgOrderParts(ByVal para As Dictionary(Of String, String)) As uMESProcessResult
        Dim sb As New StringBuilder
        Dim sbb As New StringBuilder
        sb.AppendLine(<string>
                SELECT DISTINCT p2.PRODUCTNAME ,p.DESCRIPTION ,p3.PRODUCTTYPENAME ,m.MFGORDERNAME ,o.ORDERTYPENAME ,pf.PRODUCTFAMILYNAME,u.ATTRIBUTEVALUE
	            ,e.CONTAINERNO,m.FACTORYMONTH,
	            to_char(m.PlannedStartDate,'yyyy-MM-dd')PlannedStartDate,to_char(m.PlannedCompletionDate,'yyyy-MM-dd') PlannedCompletionDate,
	            to_char(m.FACTORYCOMPLETIONDATE,'yyyy-MM-dd')FACTORYCOMPLETIONDATE ,to_char(m.FACTORYSTARDATE,'yyyy-MM-dd')FACTORYSTARDATE ,1 QTY
	            FROM ENGINEPARTPLAN e 
	            LEFT JOIN MFGORDER m ON m.MFGORDERID =e.MFGORDERID 
	            LEFT JOIN ORDERTYPE o ON o.ORDERTYPEID =m.ORDERTYPEID 
	            LEFT JOIN Userattribute u ON u.parentid = m.mfgorderid AND u.userattributename='发动机' 
	            LEFT JOIN PRODUCT p ON p.PRODUCTID =e.PRODUCTID 
	            LEFT JOIN PRODUCTBASE p2 ON p2.PRODUCTBASEID =p.PRODUCTBASEID
	            LEFT JOIN PRODUCTTYPE p3 ON p3.PRODUCTTYPEID =p.PRODUCTTYPEID
	            LEFT JOIN product pp ON pp.productid = GET_SPLIT_MFGORDER_PRODUCTID(m.mfgorderid)
	            LEFT JOIN Workflowproductfamilylist wff ON wff.workflowid=pp.workflowid 
	            LEFT JOIN productfamily pf ON wff.productfamilylistid=pf.productfamilyid
	            WHERE p3.PRODUCTTYPENAME='部件' AND e.USECOMPLETED ='1' --部件阶段报工完成，且部件没有集件
                      </string>.Value)
        sbb.AppendLine(<string>
                SELECT DISTINCT e.CONTAINERNO FROM IssueMaterialTransport ISS 
		        LEFT JOIN ENGINEPARTPLAN e ON e.ENGINEPARTPLANID=iss.ENGINEPARTPLANID
		        LEFT JOIN MFGORDER m ON m.MFGORDERID =e.MFGORDERID
		        WHERE e.CONTAINERNO IS NOT NULL 
                       </string>)
        If para.ContainsKey("ContainerNo") Then '任务单号
            If Not String.IsNullOrWhiteSpace(para("ContainerNo")) Then
                sb.AppendFormat(" and e.CONTAINERNO like '%{0}%' ", para("ContainerNo"))
                sbb.AppendFormat(" and e.CONTAINERNO like '%{0}%' ", para("ContainerNo"))
            End If
        End If
        If para.ContainsKey("MfgOrdername") Then '订单号
            If Not String.IsNullOrWhiteSpace(para("MfgOrdername")) Then
                sb.AppendFormat(" and m.MFGORDERNAME like '%{0}%' ", para("MfgOrdername"))
                sbb.AppendFormat(" and m.MFGORDERNAME like '%{0}%' ", para("MfgOrdername"))
            End If
        End If
        If para.ContainsKey("YearMonth") Then '年月
            If Not String.IsNullOrWhiteSpace(para("YearMonth")) Then
                sb.AppendFormat(" and m.FACTORYMONTH='{0}'", para("YearMonth"))
                sbb.AppendFormat(" and m.FACTORYMONTH='{0}'", para("YearMonth"))
            End If
        End If
        sb.AppendFormat("AND e.CONTAINERNO NOT IN({0})", sbb.ToString)
        sb.AppendLine("ORDER BY m.FACTORYMONTH,m.MFGORDERNAME,p2.PRODUCTNAME")

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = sb.ToString
        Return DatabaseHelper.RunProcedureForPage(paraPage)
    End Function

End Class
